﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using DTO;

namespace DAL
{
    public class ConceptDAL
    {
        public static bool InsertConcept(Concept curConcept)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertConcept";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@ConceptID";
            param.Value = curConcept.ID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@FeatureVector";
            param.Value = curConcept.FeatureVector.ToString();
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static bool InsertCommonTerm(Keyword curKeyword)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertCommonTerm";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MetaID";
            param.Value = curKeyword.Id;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static bool InsertRelationship(Relationship curRelationship)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertRelationship";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Concept1ID";
            param.Value = curRelationship.Concept1ID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Concept2ID";
            param.Value = curRelationship.Concept2ID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Score";
            param.Value = curRelationship.Score;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static bool UpdateWebpageConceptID(int webpageID, int conceptID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "UpdateWebpageConcept";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = webpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ConceptID";
            param.Value = conceptID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        public static List<Relationship> GetAllRelationships()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetAllRelationships";

            List<Relationship> relationshipList = new List<Relationship>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Relationship curRelationship = new Relationship();
                curRelationship.Concept1ID = (int)dr["Concept1"];
                curRelationship.Concept2ID = (int)dr["Concept2"];
                curRelationship.Score = (int)dr["Score"];
                relationshipList.Add(curRelationship);
            }
            return relationshipList;
        }

        public static List<Relationship> GetRelationshipByID(int curConceptID)
        { 
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetRelationshipByID";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@ConceptID";
            param.Value = curConceptID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            List<Relationship> relationshipList = new List<Relationship>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Relationship curRelationship = new Relationship();
                curRelationship.Concept1ID = (int)dr["Concept1"];
                curRelationship.Concept2ID = (int)dr["Concept2"];
                curRelationship.Score = (int)dr["Score"];
                relationshipList.Add(curRelationship);
            }
            return relationshipList;
        }
    }
}
